package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

@Service
public class QCExperimentalManagementService {
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	/**
	 * 获取实验信息
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getExpDataList(Map<String, String> parm) throws Exception{
		String sql="select t2.s_domaincaption as MONITORTYPENAME,t3.companyname,t1.datetime,t1.exp_number,t1.samplenumber,t1.exp_photo,t4.s_domaincaption as QCTypeName,count(t.exp_number||t.exp_param)as detailCount,t5.personname as personname from qc_expdetaildata t"
				+" left join qc_expdata t1 on t.exp_number = t1.exp_number left join DATA_DEFULATDOMAIN t2 on t1.monitortypecode = t2.s_domaincode and t2.s_domainname = '质控监测类型'"
				+" left join qc_companyinfo t3 on t1.qccompanycode = t3.companycode  left join DATA_DEFULATDOMAIN t4 on t1.qctypecode = t4.s_domaincode and t4.s_domainname = '质控类型'"
				+" left join (select t.exp_number,wmsys.wm_concat(distinct t1.personname) as personname from QC_EXPNUMBER_PERSON t"
				+" left join  qc_companypersoninfo t1 on t1.personcode = t.exp_personcode and t1.persontype ='0'"
				+" group by t.exp_number) t5 on t5.exp_number = t1.exp_number where 1=1 ";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sql +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段
			sql +=" and t3.companytype in("+ParamPartition(parm.get("MonitorMethod"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sql +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sql +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sql +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sql+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sql+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}
		sql+=" group by t1.exp_number,t1.datetime,t2.s_domaincaption,t3.companyname,t1.samplenumber,t1.exp_photo,t4.s_domaincaption,t1.monitortypecode,qccompanycode,t5.personname"
				+" order by t1.monitortypecode,t1.qccompanycode,t1.datetime,t1.exp_number";
		
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleList = DataUtil.getSelectRows(sql, listParam);
		return SampleList;
		
	}
	/**
	 * 获取实验详情信息
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getExpDetailList(Map<String, String> parm) throws Exception{		
		String sqldetail="select t5.monitorparamname,t.exp_mvalue,t.exp_rd,t3.methodname from qc_expdetaildata t"
					+ " left join qc_expdata t1 on t1.exp_number = t.exp_number"
					+ " left join qc_expmethod t3 on t.exp_methodcode = t3.methodcode"
					+ " left join qc_companyinfo t4 on t4.companycode = t1.qccompanycode"
					+ " left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
					+ " on t.guid = t1.paramguid) t5 on t5.monitorparamcode = t.exp_param"
					+ " and t5.monitortypecode = t1.monitortypecode and t5.companycode=t1.qccompanycode where 1=1";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sqldetail +=" and t1.monitortypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段
			sqldetail +=" and t4.companytype in("+ParamPartition(parm.get("MonitorMethod"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sqldetail +=" and t1.qccompanycode in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sqldetail +=" and t1.qctypecode in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORPARAMCODE"))){//监测项目
			sqldetail +=" and t.exp_param in("+ParamPartition(parm.get("MONITORPARAMCODE"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sqldetail+=" and t1.datetime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sqldetail+=" and t1.datetime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}	
		sqldetail+=" order by t1.monitortypecode,t1.qccompanycode,t1.datetime,t1.exp_number,t5.monitorparamname";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleDetailList = DataUtil.getSelectRows(sqldetail, listParam);
		return SampleDetailList;
		
	}
	
	public String ParamPartition(String paramData){
		String[] param=paramData.split(",");
		String StrParam="";
		for(int i=0;i<param.length;i++){
			if(param.length-i==1){
				StrParam+="'"+param[i]+"'";
			}else{
				StrParam+="'"+param[i]+"',";
			}			
		}
		return StrParam;
	}
}
